package org.example.dao;

import com.mysql.cj.Session;
import org.example.db.JDBCUtil;
import org.example.javabean.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    public List<Book> selectAll(int pageNum, int pageSize) {
        String sql = "select books.*,IF(status=1,'已借阅','未借阅') as statuss,book_sort.name as sort from  books, book_sort where books.sort_id=book_sort.id limit ?,?";
        List<Book> books = new ArrayList<>();
        try (ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql,
                new Object[]{(pageNum - 1) * pageSize, pageSize})) {
            while (rs.next()) {
                Book book = new Book(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("author"),
                        rs.getString("sort"),
                        rs.getString("description"),
                rs.getString("statuss"));
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }




    public List<History> selectHistory(int pageNum, int pageSize,String username) {
        String sql = "select books.*,book_sort.name as sort,borrow_books.borrow_date,borrow_books.end_date," +
                "borrow_books.illegal from books,borrow_books,borrow_card,book_sort where books.id=borrow_books.book_id " +
                "and books.sort_id=book_sort.id and borrow_card.username=borrow_books.card_id and borrow_card.username=? limit ?,?";
        List<History> historys = new ArrayList<>();
        try (ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql,new Object[]{username,(pageNum - 1) * pageSize, pageSize})) {
            while (rs.next()) {
               History history = new History(
                       rs.getString("id"),
                       rs.getString("name"),
                        rs.getString("author"),
                        rs.getString("sort"),
                        rs.getString("description"),
               rs.getDate("borrow_date"),
                rs.getDate("end_date"),
                rs.getString("illegal"));
                historys.add(history);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return historys;
    }


    public int selectAllCount() {
        String sql = "select count(*) as num from books";
        try (final ResultSet rs =
                     JDBCUtil.getInstance().executeQueryRS(sql,
                             new Object[]{})) {
            while (rs.next()) {
                return rs.getInt("num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
        return 0;
    }

    public int selectHistoryCount(String username) {
        String sql = "select COUNT(*) as num from books,borrow_books,borrow_card,book_sort where books.id=borrow_books.book_id and books.sort_id=book_sort.id and borrow_card.username=borrow_books.card_id and borrow_card.username=?";
        try (final ResultSet rs =
                     JDBCUtil.getInstance().executeQueryRS(sql,
                             new Object[]{username})) {
            while (rs.next()) {
                return rs.getInt("num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
        return 0;
    }
    public int insertBorrowBook(String card_id,String book_id) {
       int result=0;
        String sql = "INSERT into borrow_books VALUES(id,?,?,now(),DATE_ADD(borrow_date,INTERVAL 10 DAY),DATE_ADD(borrow_date,INTERVAL 20 DAY),'未逾期','chen')";
        try
        {
            result = JDBCUtil.getInstance().executeUpdate(sql,new Object[]{card_id,book_id});
        }
    catch (Exception e) {
            e.printStackTrace();

        }
        return result;
    }

    public int selectCollectionCount(String username) {
        String sql = "select COUNT(*) as num from books,collection where collection.book_id=books.id  and collection.username=?";
        try (final ResultSet rs =
                     JDBCUtil.getInstance().executeQueryRS(sql,
                             new Object[]{username})) {
            while (rs.next()) {
                return rs.getInt("num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
        return 0;
    }

  public int updateBorrowedStatus(String book_id)
  {
      int result=0;
      String sql = "\n" +
              "update books set books.`status`=1 where books.id=?";
      try
      {
          result = JDBCUtil.getInstance().executeUpdate(sql,new Object[]{book_id});
      }
      catch (Exception e) {
          e.printStackTrace();

      }
      return result;
  }
  public int deleteHistory(String username,String book_id)
  {
      int result =0;
      String sql="delete from borrow_books where card_id=? and book_id=?";
      try
      {
          result = JDBCUtil.getInstance().executeUpdate(sql,new Object[]{username,book_id});
      }
      catch (Exception e) {
          e.printStackTrace();

      }
      return result;
  }
    public int returnBook(String username,String book_id)
    {
        int result =0;
        String sql="delete from borrow_books where card_id=? and book_id=?";
        try
        {
            result = JDBCUtil.getInstance().executeUpdate(sql,new Object[]{username,book_id});
        }
        catch (Exception e) {
            e.printStackTrace();

        }
        return result;
    }
    public int updateReturnStatus(String book_id)
    {
        int result=0;
        String sql = "\n" +
                "update books set books.`status`=0 where books.id=?";
        try
        {
            result = JDBCUtil.getInstance().executeUpdate(sql,new Object[]{book_id});
        }
        catch (Exception e) {
            e.printStackTrace();

        }
        return result;
    }


    public List<Collection> selectAllCollection(String username,int pageNum, int pageSize) {
        String sql = "select books.*,book_sort.name as sort,IF(books.status=1,'已借阅','未借阅') as statuss from books,collection,book_sort where collection.username=? and collection.book_id=books.id and books.sort_id=book_sort.id limit ?,?";
        List<Collection> collections = new ArrayList<>();
        try (ResultSet rs = JDBCUtil.getInstance().executeQueryRS(sql,
                new Object[]{username,pageNum-1,pageSize})) {
            while (rs.next()) {
                Collection collection = new Collection(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("author"),
                        rs.getString("sort"),
                        rs.getString("description"),
                       rs.getString("statuss"));
                collections.add(collection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return collections;
    }



    public int insertCollectionBook(String username,String book_id) {
        int result=0;
        String sql = "\n" +
                "insert INTO collection VALUES(?,?,1)";
        try
        {
            result = JDBCUtil.getInstance().executeUpdate(sql,new Object[]{username,book_id});
        }
        catch (Exception e) {
            e.printStackTrace();

        }
        return result;
    }


    public int cancelCollection(String username,String book_id)
    {
        int result =0;
        String sql="delete  from collection WHERE username=? and book_id=?";
        try
        {
            result = JDBCUtil.getInstance().executeUpdate(sql,new Object[]{username,book_id});
        }
        catch (Exception e) {
            e.printStackTrace();

        }
        return result;
    }

}